from flask import Flask, render_template, request, redirect, url_for, session
import pandas as pd
from datetime import timedelta, datetime
import sqlite3
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///blog.db'
app.config['SECRET_KEY'] = 'dxjdfhdhfytfhdughd'
app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(days=7)
db = SQLAlchemy(app)


class Message(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    date = db.Column(db.DateTime, default=datetime.now)


def conn_sqlite():
    conn = sqlite3.connect('user.db')
    cur = conn.cursor()
    return conn, cur


def insert_sqlite(sql):
    conn, cur = conn_sqlite()
    cur.execute(sql)
    conn.commit()


def delete_sqlite(sql):
    conn, cur = conn_sqlite()
    cur.execute(sql)
    conn.commit()


def select_sqlite(sql):
    conn, cur = conn_sqlite()
    cur.execute(sql)
    result = cur.fetchall()
    record = []
    for i in result:
        record.append(i)
    return record


@app.route('/login', methods=['POST', 'GET'])
def login():
    # sql = '''INSERT INTO user(username,password,role) VALUES('熊三','999','大哥用户')'''
    # insert_sqlite(sql)
    sql = '''CREATE TABLE IF NOT EXISTS blog(
        id integer PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        content TEXT NOT NULL,
        date TEXT)
        '''
    # sql = "delete from user"
    insert_sqlite(sql)
    sql1 = '''SELECT * FROM user'''
    data = select_sqlite(sql1)
    # print(data)
    users = request.form.get('user')
    password = request.form.get('password')
    for i in range(len(data)):
        if users == data[i][1] and password == data[i][2]:
            role = data[i][3]
            session.permanent = True
            session['username'] = users
            session['role'] = role
            return redirect(url_for("index"))

    return render_template('login.html')


@app.route('/logot', methods=['GET'])
def logot():
    session.pop('username')
    return redirect(url_for('login'))


@app.route('/', methods=['POST', 'GET'])
def index():
    sql = '''SELECT * FROM user'''
    data = select_sqlite(sql)
    return render_template('index.html', user=data)


@app.route('/myweb', methods=['POST', 'GET'])
def myweb():
    if request.method == 'POST':
        user1 = request.form.get('user1')
        password = request.form.get('password')
        role = request.form.get('role')
        sql = "select * from user"
        users = select_sqlite(sql)
        if user1 != "" and password == "":
            sql = "delete from user where username='%s'" % user1
            delete_sqlite(sql)
        else:
            for i in users:
                if user1 == i[1] and password == i[2] and role == i[3]:
                    break
            else:
                sql1 = "insert into user(username,password,role) values('%s','%s','%s')" % (user1, password, role)
                insert_sqlite(sql1)
    sql = "select * from user"
    user = select_sqlite(sql)
    return render_template('myweb.html', user=user)


@app.route('/add_date', methods=['POST', 'GET'])
def add_message():
    sss = Message(name='湛某', content='789')
    db.session.add(sss)
    db.session.commit()
    dates = Message.query.all()  # 查询所有
    for date in dates:
        print(date.name, date.content)
    return '数据添加成功！'


@app.route('/score', methods=["POST", "GET"])
def score():
    names = request.form.get('name')
    user = pd.read_excel('cjd.xlsx')
    data = user[user['姓名'] == names]
    return render_template('score.html', user=data.to_html(index=False))


@app.route('/about', methods=['POST', 'GET'])
def about():
    return render_template('about.html')


@app.route('/laili', methods=['POST', 'GET'])
def laili():
    date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    return render_template('laili.html', date=date)


@app.route('/xisu', methods=['POST', 'GET'])
def xisu():
    date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    return render_template('xisu.html', date=date)


@app.route('/zhufu', methods=['POST', 'GET'])
def zhufu():
    date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    return render_template('zhufu.html', date=date)


@app.route('/tupian', methods=['POST', 'GET'])
def tupian():
    date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    return render_template('tupian.html', date=date)


@app.route('/gushi', methods=['POST', 'GET'])
def gushi():
    date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    return render_template('gushi.html', date=date)


@app.route('/wish', methods=['POST', 'GET'])
def wish():
    if request.method == 'POST':
        content = request.form.get('task')
        # date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        # # sql = 'INSERT INTO blog(name,content,date) VALUES("12456","23456","45679-56-234 2:2:23567")'
        # sql = 'INSERT INTO blog(name,content,date) VALUES("%s","%s","%s")' % (session['username'], content, date)
        # insert_sqlite(sql)
        adds = Message(name=session['username'], content=content)
        db.session.add(adds)
        db.session.commit()
        # sql1 = 'SELECT * FROM blog'
        # record = select_sqlite(sql1)
        record = Message.query.order_by(Message.id.desc()).all()
        return render_template('wish.html', record=record)
    else:
        record = Message.query.order_by(Message.id.desc()).all()
        for i in record:
            print(i)
        return render_template('wish.html', record=record)


@app.route('/delete/<int:id>', methods=['POST', 'GET'])
def delete_message(id):
    # data = Message.query.filter_by(id='%s').first() % id
    # db.session.delete(data)
    # db.session.commit()
    article = Message.query.get(id)
    db.session.delete(article)
    db.session.commit()
    return redirect(url_for('wish'))


if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(debug=True, host='0.0.0.0')
